package com.course.daoImpl;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;



import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;

import com.course.daoI.UserDAoI;
import com.course.dbutil.DBConnectionFactory;
import com.course.dbutil.SqlConstants;
import com.course.formbean.ProfileFormBean;
import com.course.util.CoreHash;
import com.course.util.CoreList;
import com.course.util.DateUtil;
import com.course.util.LoggerManager;

public class UserDaoImpl implements UserDAoI{

	
	PreparedStatement pstmt;
	Statement stmt;
	ResultSet rs,rs1;
	Connection con;
	
	
	public UserDaoImpl()
	{
		con=DBConnectionFactory.getConnection();
		
	}
	public CoreList getEmpDetails(String path)
    {
    	CoreList aCoreList = new CoreList();
    	
    	String userid="";
    	String ctitle="";
    	ProfileFormBean rb=null;
		aCoreList.clear();
		Statement st;
		try {
		
			st = con.createStatement();
			int count = 0;
			ResultSet rs = st.executeQuery(SqlConstants._GET_EMPLOYEE);
			while(rs.next())
			{
				
				rb=new ProfileFormBean();
				userid=rs.getString(1);
				//inventory.setBrandID(rs.getInt(1));
				rb.setId(userid);
				rb.setLogin(rs.getString(2));
				rb.setFirstname(rs.getString(3));
				rb.setLastname(rs.getString(4));
				rb.setBdate(rs.getString(5));
				rb.setEmail(rs.getString(6));
				rb.setFax(rs.getString(7));
				Blob b=rs.getBlob(8);
				byte b1[]=b.getBytes(1,(int)b.length());//new byte[(int)b.length()];
				//InputStream in=photo.getBinaryStream();
				OutputStream fout=new FileOutputStream(path+"/"+ userid+".jpg");
				fout.write(b1);
			
				rb.setPhoto(userid+".jpg");
				
				rb.setQualification(rs.getString(9));
				
				int cid=rs.getInt(10);
				
				rb.setCourseid(cid);
				pstmt=con.prepareStatement(SqlConstants._GET_COURSE_TITLE);
				pstmt.setInt(1, cid);
				
				rs1=pstmt.executeQuery();
				if(rs1.next())
				{
					
				   ctitle=rs1.getString(1);
					}
				rb.setCtitle(ctitle);
				
				count++;
				aCoreList.add(rb);
			}	
    	}
    	catch(SQLException se)
    	{
    		LoggerManager.writeLogWarning(se);
    	}
    	catch(Exception e)
    	{
    		LoggerManager.writeLogWarning(e);
    	}
    	finally
    	{
    		try
    		{
    			con.close();
    		}
    		catch(SQLException se)
    		{
    			LoggerManager.writeLogWarning(se);
    		}
    	}
    	return aCoreList;
    }  
		
	public CoreList getFaculty(String path,int courid)
    {
    	CoreList aCoreList = new CoreList();
    
    	String userid="";
    	String ctitle="";
    	ProfileFormBean rb=null;
		aCoreList.clear();
		Statement st;
		try {
			
			st = con.createStatement();
			int count = 0;
			System.out.println("in DAOImpl path is..............."+path);
			System.out.println("in DAop Class courseid is//////////////////////////"+courid);
			ResultSet rs = st.executeQuery("select USERID,LOGINTYPE,firstname,lastname,to_char(dob),emailid,faxno,photograph,qualification,courseid from userdetails where logintype='faculty'and courseid="+courid);
			while(rs.next())
			{
				
				rb=new ProfileFormBean();
				userid=rs.getString(1);
				//inventory.setBrandID(rs.getInt(1));
				rb.setId(userid);
				rb.setLogin(rs.getString(2));
				rb.setFirstname(rs.getString(3));
				rb.setLastname(rs.getString(4));
				rb.setBdate(rs.getString(5));
				rb.setEmail(rs.getString(6));
				rb.setFax(rs.getString(7));
				Blob b=rs.getBlob(8);
				byte b1[]=b.getBytes(1,(int)b.length());//new byte[(int)b.length()];
				//InputStream in=photo.getBinaryStream();
				OutputStream fout=new FileOutputStream(path+"/"+ userid+".jpg");
				fout.write(b1);
			
				rb.setPhoto(userid+".jpg");
				
				rb.setQualification(rs.getString(9));
				
				int cid=rs.getInt(10);
				
				rb.setCourseid(cid);
				pstmt=con.prepareStatement(SqlConstants._GET_COURSE_TITLE);
				pstmt.setInt(1, cid);
				
				rs1=pstmt.executeQuery();
				if(rs1.next())
				{
					
				   ctitle=rs1.getString(1);
					}
				rb.setCtitle(ctitle);
				
				count++;
				aCoreList.add(rb);
			}	
    	}
    	catch(SQLException se)
    	{
    		LoggerManager.writeLogWarning(se);
    	}
    	catch(Exception e)
    	{
    		LoggerManager.writeLogWarning(e);
    	}
    	finally
    	{
    		try
    		{
    			con.close();
    		}
    		catch(SQLException se)
    		{
    			LoggerManager.writeLogWarning(se);
    		}
    	}
    	return aCoreList;
    }  
		
		
	public CoreList getStudents(String path)
    {
		CoreList aCoreList = new CoreList();
     
    	String userid="";
    	String ctitle="";
    	ProfileFormBean rb=null;
		aCoreList.clear();
		Statement st;
		try {
			
			st = con.createStatement();
			int count = 0;
			ResultSet rs = st.executeQuery(SqlConstants._GET_STUDENTS);
			while(rs.next())
			{
				
				rb=new ProfileFormBean();
				userid=rs.getString(1);
				//inventory.setBrandID(rs.getInt(1));
				rb.setId(userid);
				rb.setLogin(rs.getString(2));
				rb.setFirstname(rs.getString(3));
				rb.setLastname(rs.getString(4));
				rb.setBdate(rs.getString(5));
				rb.setEmail(rs.getString(6));
				rb.setFax(rs.getString(7));
				Blob b=rs.getBlob(8);
				byte b1[]=b.getBytes(1,(int)b.length());//new byte[(int)b.length()];
				//InputStream in=photo.getBinaryStream();
				OutputStream fout=new FileOutputStream(path+"/"+ userid+".jpg");
				fout.write(b1);
			
				rb.setPhoto(userid+".jpg");
				
				rb.setQualification(rs.getString(9));
				
				int cid=rs.getInt(10);
				
				rb.setCourseid(cid);
				pstmt=con.prepareStatement(SqlConstants._GET_COURSE_TITLE);
				pstmt.setInt(1, cid);
				
				rs1=pstmt.executeQuery();
				if(rs1.next())
				{
					
				   ctitle=rs1.getString(1);
					}
				rb.setCtitle(ctitle);
				
				count++;
				aCoreList.add(rb);
			}	
    	}
    	catch(SQLException se)
    	{
    		LoggerManager.writeLogWarning(se);
    	}
    	catch(Exception e)
    	{
    		LoggerManager.writeLogWarning(e);
    	}
    	finally
    	{
    		try
    		{
    			con.close();
    		}
    		catch(SQLException se)
    		{
    			LoggerManager.writeLogWarning(se);
    		}
    	}
    	return aCoreList;
    }  
		
		
	public CoreList getStudentsInfo(String path,int courid)
    {
    	CoreList aCoreList = new CoreList();
    	
    	String userid="";
    	String ctitle="";
    	ProfileFormBean rb=null;
		aCoreList.clear();
		Statement st;
		try {
			
			st = con.createStatement();
			int count = 0;
			System.out.println("in Dao path is..............."+path);
			System.out.println("In Dao courseid is..............."+courid);
			ResultSet rs = st.executeQuery("select USERID,LOGINTYPE,firstname,lastname,to_char(dob),emailid,faxno,photograph,qualification,courseid from userdetails where logintype='student' and courseid="+courid);
			while(rs.next())
			{
				
				rb=new ProfileFormBean();
				userid=rs.getString(1);
				//inventory.setBrandID(rs.getInt(1));
				rb.setId(userid);
				rb.setLogin(rs.getString(2));
				rb.setFirstname(rs.getString(3));
				rb.setLastname(rs.getString(4));
				rb.setBdate(rs.getString(5));
				rb.setEmail(rs.getString(6));
				rb.setFax(rs.getString(7));
				Blob b=rs.getBlob(8);
				byte b1[]=b.getBytes(1,(int)b.length());//new byte[(int)b.length()];
				//InputStream in=photo.getBinaryStream();
				OutputStream fout=new FileOutputStream(path+"/"+ userid+".jpg");
				fout.write(b1);
			
				rb.setPhoto(userid+".jpg");
				
				rb.setQualification(rs.getString(9));
				
				int cid=rs.getInt(10);
				
				rb.setCourseid(cid);
				pstmt=con.prepareStatement(SqlConstants._GET_COURSE_TITLE);
				pstmt.setInt(1, cid);
				
				rs1=pstmt.executeQuery();
				if(rs1.next())
				{
					
				   ctitle=rs1.getString(1);
					}
				rb.setCtitle(ctitle);
				
				count++;
				aCoreList.add(rb);
			}	
    	}
    	catch(SQLException se)
    	{
    		LoggerManager.writeLogWarning(se);
    	}
    	catch(Exception e)
    	{
    		LoggerManager.writeLogWarning(e);
    	}
    	finally
    	{
    		try
    		{
    			con.close();
    		}
    		catch(SQLException se)
    		{
    			LoggerManager.writeLogWarning(se);
    		}
    	}
    	return aCoreList;
    }  
		
		
		
		
	public boolean deleteFaculty(int cid,String login)
	{
		int uid=0;
		boolean flag=true;
		try
		{
			
			pstmt=con.prepareStatement(SqlConstants._DELETE_USER);
			
			
			
			stmt=con.createStatement();
			  rs=stmt.executeQuery("select userid from userdetails where courseid="+cid);
			  
			  System.out.println("in DAo login is ....,,,,,,,,,,,"+login);
			  
			  if(rs.next())
			  {
				 uid=rs.getInt(1);
				 
				 System.out.println("in Dao userid is.............."+uid);
			  }
			
			pstmt.setInt(1, uid);
			pstmt.setInt(2, cid);
			
			
			int delete=pstmt.executeUpdate();
			
			if(delete>0)
	        {
	        
	         con.commit();
	        }
	        else
	        {
	       	 flag=false;
	       	 con.rollback();
	        }

	   } 
	   
	   catch (Exception e) 
	   {
	       e.printStackTrace();
	       flag=false;
	       try 
	       {
	           con.rollback();
	       } 
	       catch (SQLException se) 
	       {
	           se.printStackTrace();
	       }
	   }
	   
	   finally
		{
		 try{
			 if(con!=null)
				 con.close();				 
		 }
		 catch(Exception e){}
		}
	return flag;
	}
		
	
	
	public boolean deleteStudent(int cid,int uid)
	{
		
		boolean flag=true;

		try
		{
			
			pstmt=con.prepareStatement(SqlConstants._DELETE_STUDENT);
			
			pstmt.setInt(1, uid);
			pstmt.setInt(2, cid);
			
			
			int delete=pstmt.executeUpdate();
			
			if(delete>0)
	        {
	        
	         con.commit();
	        }
	        else
	        {
	       	 flag=false;
	       	 con.rollback();
	        }

	   } 
	   
	   catch (Exception e) 
	   {
	       e.printStackTrace();
	       flag=false;
	       try 
	       {
	           con.rollback();
	       } 
	       catch (SQLException se) 
	       {
	           se.printStackTrace();
	       }
	   }
	   
	   finally
		{
		 try{
			 if(con!=null)
				 con.close();				 
		 }
		 catch(Exception e){}
		}
	return flag;
	}
	
	public CoreHash getReportPresent()
	{
		
		
		CoreHash aCoreHash = new CoreHash();
		aCoreHash.clear();
		System.out.println("aCoreHash--"+aCoreHash.isEmpty());
		int sno=1;
		CallableStatement cstmt;
		ProfileFormBean aProfile=null;
		try {
			
			   
			
            	 cstmt=con.prepareCall("{call REF_CURSOR_TEST.GET_ACCOUNTS_PROCEDURE(?)}");
            	 cstmt.registerOutParameter(1,OracleTypes.CURSOR);
            	 cstmt.executeUpdate();
            	 ResultSet rs=((OracleCallableStatement)cstmt).getCursor(1);
            	  while(rs.next())
                 {
              	   aProfile=new ProfileFormBean();
              	   
              	   aProfile.setLoginID(rs.getString(1));
              	   aProfile.setFirstname(rs.getString(2));  
              	   aProfile.setLastName(rs.getString(3));
              	   String date=DateUtil.parseDate(rs.getDate(4)).trim();
                	aProfile.setBirthDate(date);
                   aProfile.setRegDate(DateUtil.parseDate(rs.getDate(5)).trim());
                   aProfile.setEmail(rs.getString(6));
                   	
               	   
              	 aCoreHash.put(new Integer(sno),aProfile);
 			    sno++;
 		  
                 }
		}
		catch(SQLException e)
		{e.printStackTrace();
			LoggerManager.writeLogWarning(e);
		}
		finally
		{
		 try{
			 if(con!=null)
				 con.close();
			 
		 }
		 catch(SQLException e){}
		}
		return aCoreHash;
	}
}
